package com.util;



import java.io.InputStream;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.util.*;

public class SimpleJdbcUtil {

    private static String driver = "";
    private static String url = "";
    private static String userName = "";
    private static String password = "";

    static {
        Properties props = new Properties();
        try {
            InputStream is = SimpleJdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
            props.load(is);
            driver = props.getProperty("driver");
            url = props.getProperty("url");
            userName = props.getProperty("userName");
            password = props.getProperty("password");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static String getDriver() {
        return driver;
    }

    public static String getUrl() {
        return url;
    }

    public static String getUserName() {
        return userName;
    }

    public static String getPassword() {
        return password;
    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, userName, password);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return conn;
    }

    public static void close(ResultSet rs, Statement stat, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
                rs = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (stat != null) {
            try {
                stat.close();
                stat = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
                conn = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /*
     *    批量执行插入、更新、删除
     */
    public static int batchExecute(String sql, Object[][] params) {
        int ret = -1;
        Connection conn = null;
        PreparedStatement stat = null;
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            if (params != null) {
                for (int i = 0, n = params.length; i < n; i++) {
                    for (int j = 0, m = params[i].length; j < m; j++) {
                        stat.setObject(j + 1, params[i][j]);
                    }
                    stat.addBatch();
                }
            } else { // 这里如果不执行 addBatch() 方法，则执行后不会有任何结果
                stat.addBatch();
            }

            int[] rowNumList = stat.executeBatch();
            if (rowNumList != null && rowNumList.length > 0) {
                ret = 0;
                for (int num : rowNumList) {
                    ret += num;
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(null, stat, conn);
        }
        return ret;
    }

    /*
     * 单条记录的插入、删除、更新
     */
    public static int execute(String sql, Object[] params) {
        if (params == null) {
            return batchExecute(sql, null);
        } else {
            Object[][] wrapedParams = {params};
            return batchExecute(sql, wrapedParams);
        }
    }

    /*
     *    获取实体对象
     */
    public static <T> List<T> queryResult(String sql, Object[] args, Class<T> clazz) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        List<T> list = new ArrayList<T>();
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            if (args != null) {
                for (int i = 0, n = args.length; i < n; i++) {
                    stat.setObject(i + 1, args[i]);
                }
            }

            rs = stat.executeQuery();

            ResultSetMetaData meta = rs.getMetaData();
            int totalProps = meta.getColumnCount();

            // 获取单列查询结果, 通常是 COUNT 等统计函数的结果
            if (totalProps == 1) {

                rs.next();
                list.add((T) rs.getObject(1));

            } else if (clazz != null) { // 获取多列查询结果，通常是获取实体类

                Method[] methods = clazz.getDeclaredMethods();
                Map<String, Method> methodNameMap = new HashMap<String, Method>();
                for (Method m : methods) {
                    String methodName = m.getName().toLowerCase();
                    if (!methodName.startsWith("set")) {
                        continue;
                    }
                    int mod = m.getModifiers();
                    boolean isInstancePublicSetter = Modifier.isPublic(mod) && !Modifier.isStatic(mod) && !Modifier.isAbstract(mod);
                    if (isInstancePublicSetter) {
                        methodNameMap.put(methodName, m);
                    }
                }

                while (rs.next()) {
                    Object obj = clazz.newInstance();
                    for (int i = 1; i <= totalProps; i++) {
                        Object currentColumnVal = rs.getObject(i);
                        String currentColumnName = meta.getColumnName(i);
                        Method m = methodNameMap.get("set" + currentColumnName);
                        if (m != null) {
                            m.invoke(obj, currentColumnVal);
                        }
                    }
                    list.add((T) obj);
                }

            } else { // 异常情形：既不是单列结果，也没有指明实体类的类型
                throw new Exception("queryResult(String sql, Object[] args, Class<T> clazz) 非单列结果且未指明clazz的值");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /*
     * 获取统计函数的查询结果
     */
    public static Number getSimpleResult(String sql, Object[] params) {
        Number ret = -1;
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);
            if (params != null) {
                for (int i = 0, n = params.length; i < n; i++) {
                    stat.setObject(i + 1, params[i]);
                }
            }

            rs = stat.executeQuery();
            if (rs.next()) {
                ret = (Number) rs.getObject(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(rs, stat, conn);
        }
        return ret;
    }

}
